﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{ 
    public class HLTVV06Data : BaseFunction
    {

        byte[] b;
        string value;
        public HLTVV06Data()
            : base()
        {

        }
        /*    public DataSet LoadTableStructure(DataSet dsTarget)
            {
                try
                {
                    TableQueue.Enqueue("WAFARBEN");
                    TableQueue.Enqueue("SELECT WAFART as ArtNum,	WAFVAR as ArtVar,	 WAFZUSATZ as ArtColor,WAFBEZ as ColorDescription"
                        + "  FROM INTEX.WAFARBEN WHERE RowNum<1");
                     TableQueue.Enqueue("BSSATZ");
                     TableQueue.Enqueue("SELECT  "
                         + "BSKART"
                         + ",BSFDZ"
                         + ",BSSRFNR"
                         + ",BSFAKTOR"
                        + " FROM INTEX.BSSATZ WHERE RowNum<1");
                    TableQueue.Enqueue("BASTKE");
                    TableQueue.Enqueue("SELECT "
                                  + "BAKART"
                   + ",BAKVAR"
                   + ",BAKFARBE"
                   + ",BAKKART"
                   + ",BAKKVAR"
                   + ",BAKKFARBE"
                   + ",BAKPATT"
                   + ",BAKPOS"
                   + ",BAKANZ"
                   + ",BAKABF"
                   + " FROM INTEX.BASTKE WHERE RowNum<1");
                    TableQueue.Enqueue("BSFARB");
                    TableQueue.Enqueue("SELECT  "
                        + "BSFKART"
                        + ",BSFKVAR"
                        + ",BSFKFARBE "
                        + ",BSFBEZ"
                    + " FROM INTEX.BSFARB WHERE RowNum<1");
                    TableQueue.Enqueue("BASTGA");
                    TableQueue.Enqueue("SELECT "
                    +"BAGFDZ"
                    + ",BAGART"
                    + ",BAGVAR"
                    + ",BAGFARBE"
                    + ",BAGPOS" 
                    + ",BAGGART"
                    + ",BAGGVAR"
                    + ",BAGGFARBE"
                    + ",BAGFDZ"
                    + ",BAGKGNET"
                    + ",BAGKGBRUT"
                    +" FROM INTEX.BASTGA WHERE RowNum<1");
                    TableQueue.Enqueue("WASTGA");
                    TableQueue.Enqueue("SELECT  "
                           + "WAGART"
                    + ",WAGVAR"
                    + ",WAGWCOL"
                    + ",WAGPSA"
                    + ",WAGPOS"
                    + ",WAGGART"
                    + ",WAGGVAR"
                    + ",WAGGFARBE"
                    + ",WAGFDZ"
                    + ",WAGKGNET"
                    + ",WAGKGBRUT"
                    + ",WAGEAFAKTOR"
                    + ",WAGBEM"
                    + " FROM INTEX.WASTGA WHERE RowNum<1"); 
                    TableQueue.Enqueue("GSDAT");
                    TableQueue.Enqueue("SELECT "
                    + " GSART"
                    + " ,GSVAR"
                    + " ,GSMAT"
                    + " ,GSNUMART"
                    + " ,GSGARNNR"
                    + " ,GSFACH"
                    + " ,GSRECHNUM"
                    + " ,GSRECHNR"
                     + " FROM INTEX.GSDAT WHERE RowNum<1"); 
                    TableQueue.Enqueue("GASTGA");
                    TableQueue.Enqueue("SELECT "
                        +"GAGART"
                        +",GAGVAR"
                        +",GAGFARBE"
                        +",GAGPOS"
                        +",GAGGART"
                        +",GAGGVAR"
                        +",GAGGFARBE"
                        +",GAGKGNET"
                        + ",GAGKGBRUT"   
                       + " FROM INTEX.GASTGA Where RowNum<1"); 
                    TableQueue.Enqueue("GSCOLOR");
                    TableQueue.Enqueue("SELECT GSCART,GSCVAR,GSCFARBE,GSCBEZ FROM INTEX.GSCOLOR WHERE RowNum<1");
                    while (TableQueue.Count > 0)
                    {
                        string TableName = TableQueue.Dequeue() as string;
                        string QueryStr = TableQueue.Dequeue() as string;
                        if (!dsTarget.Tables.Contains(TableName))
                        {
                            orlReadCommand = OracleDB.CreateCommand();
                            orlReadCommand.CommandText = QueryStr;
                            setResult = Connect2Proxy(orlReadCommand);
                            if (setResult.Tables.Count > 0)
                            {
                                setResult.Tables[0].TableName = TableName;
                            }
                            foreach (DataTable dtTemp in setResult.Tables)
                            {
                                dsTarget = ImportDataTable(dsTarget, dtTemp);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                     Tracer.Error(ex.StackTrace, ex); 
                }
                return dsTarget;
            }*/
        public HLTVV06Data(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetWBBST(ref ArrayList listVV06, ref ArrayList ListColor,ref ArrayList listBatchNo,DataRow dwWBBST)
        {
            string TableName = "WBBST";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            try
            {
                /*
                  SELECT WBBAUFKRS,WBBRWLFD,WBBPOS,WBBSTKNR,WBBWAKZ,WBBART,WBBVAR,WBBFARBE,WBBBESCHICH,
WBBSSTKLG,WBBSGEW,WBBSBRT,WBBTAX,WBBBEM
 FROM WBBST W WHERE
WBBAUFKRS = '  ' AND WBBAUFLFD ='004326'  AND WBBALIEFSCH=262 AND  WBBAUFPOS = '004'
                 */
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT rownum AS CODE,WBBALIEFSCH,WBBAUFKRS,WBBAUFLFD,WBBAUFPOS"
                +",WBBRWKRS,WBBRWLFD,WBBPOS,WBBSTKNR,WBBWAKZ,WBBDPART"
                + ",WBBART,WBBVAR,WBBFARBE,WBBBESCHICH,ROUND(WBBSSTKLG/1000/0.9144,1) AS WBBSSTKLG,WBBSGEW,WBBSBRT,WBBTAX,WBBBEM"
                +" FROM WBBST W WHERE   WBBMANDANT = '00' "
                +" AND WBBAUFKRS = :WBBAUFKRS AND WBBAUFLFD =:WBBAUFLFD AND WBBAUFPOS = :WBBAUFPOS"
                + " AND WBBALIEFSCH=:WBBALIEFSCH";

                orlReadCommand.Parameters.Add("WBBAUFKRS", OracleType.NVarChar).Value = dwWBBST["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFLFD", OracleType.NVarChar).Value = dwWBBST["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFPOS", OracleType.NVarChar).Value = dwWBBST["WBBAUFPOS"].ToString();
                orlReadCommand.Parameters.Add("WBBALIEFSCH", OracleType.NVarChar).Value = dwWBBST["WBBALIEFSCH"].ToString();
                
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;
                    
                    string ColumnName = "WBBTAX";
                    string sTemp = "";
                    string WBBALIEFSCH = "", SBATCHNO = "", Color = "";
                    foreach (DataRow dwTemp in setResult.Tables[TableName].Rows)
                    {
                        ColumnName = "WBBFARBE";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        #region----------Delivery Note Number-------------------
                        ColumnName = "WBBBESCHICH";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;

                        ColumnName = "WBBALIEFSCH";
                        sTemp = dwTemp[ColumnName].ToString().Trim();
                        if (!WBBALIEFSCH.Contains(sTemp + ";"))
                        {
                            listVV06.Add(sTemp);
                            WBBALIEFSCH += sTemp + ";";
                        }
                        #endregion
                        #region--------------Batchno ------------------
                        ColumnName = "WBBDPART";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;

                        sTemp = dwTemp[ColumnName].ToString();
                        if (!SBATCHNO.Contains(sTemp + ";"))
                        {
                            listBatchNo.Add(sTemp);
                            SBATCHNO += sTemp + ";";
                        }
                        #endregion
                        #region------------------Color------------------------
                        ColumnName = "WBBTAX";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        sTemp = dwTemp[ColumnName].ToString();
                        if (!Color.Contains(sTemp + ";"))
                        {
                            ListColor.Add(sTemp);
                            Color += sTemp + ";";
                        }
                        #endregion
                    }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetCOLOR(DataRow dwWBBST)
        {
            string TableName = "COLOR";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT SUBSTR(SDKEY,3,3) AS COLOR,SUBSTR(SDDATEN,2,10) AS DES, "
                                            + " SUBSTR(SDKEY,3,3) AS COLORSD"
                                            + " FROM WBBST ,SDDAT  WHERE WBBMANDANT = SUBSTR(SDKEY,1,2) "
                                            + " AND WBBTAX = SUBSTR(SDKEY,3,3) AND SDSA = '0201' AND SUBSTR(SDKEY,24,1) = 'V' "
                                            + " AND WBBAUFKRS = :WBBAUFKRS AND WBBAUFLFD =:WBBAUFLFD "
                                            + " AND WBBAUFPOS = :WBBAUFPOS AND WBBALIEFSCH = :WBBALIEFSCH ";

                orlReadCommand.Parameters.Add("WBBAUFKRS", OracleType.NVarChar).Value = dwWBBST["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFLFD", OracleType.NVarChar).Value = dwWBBST["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("WBBAUFPOS", OracleType.NVarChar).Value = dwWBBST["WBBAUFPOS"].ToString();
                orlReadCommand.Parameters.Add("WBBALIEFSCH", OracleType.NVarChar).Value = dwWBBST["WBBALIEFSCH"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;

                    byte[] b;
                    string value;
                    string ColumnName = "COLORSD";
                    foreach (DataRow dwTemp in setResult.Tables[TableName].Rows)
                    {
                        ColumnName = "COLORSD";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                    }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetVVTLIEF(DataRow dwWBBST)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            string TableName = "VVTLIEF";

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT VVTLIEFSCH,VVTLIEFSCHPOS,VVORT,SUBSTR(SDDATEN,2,20) AS STATUS,VVVERSART,VVSPED,VVTRENR,"
                                        + " VVAVISNR,VVVERSDAT,round(VVTMTR/100/0.9144,3) AS QUANTITY,ROUND(VVTKG/1000,3) AS KG,"
                                        + "  VVTAUFKRS,VVTAUFLFD,VVTAUFPOS,'' AS WBBTAX"
                                        + " FROM VVTLIEF,VVLIEF,SDDAT  WHERE VVTMANDANT = VVMANDANT "
                                        + " AND VVTLIEFSCH = VVLIEFSCH AND VVTMANDANT = '00'"
                                        + " AND VVMANDANT = SUBSTR(SDKEY,1,2) AND VVSTATUS = SUBSTR(SDKEY,3,2) AND SDSA = '5555' "
                                        + " AND VVTAUFKRS = :VVTAUFKRS AND VVTAUFLFD = :VVTAUFLFD AND VVTAUFPOS = :VVTAUFPOS ";

                orlReadCommand.Parameters.Add("VVTAUFKRS", OracleType.NVarChar).Value = dwWBBST["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("VVTAUFLFD", OracleType.NVarChar).Value = dwWBBST["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("VVTAUFPOS", OracleType.NVarChar).Value = dwWBBST["WBBAUFPOS"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    string ColumnName = "";
                    int count = 0;
                    foreach (DataRow dwTemp in setResult.Tables[TableName].Rows)
                    {
                        count++;
                        ColumnName = "STATUS";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        ColumnName = "VVORT";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                    }
                    Tracer.Speak("共有" + count + "次发货");
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetVVVKPOS(DataRow dwWBBST)
        {

            string TableName = "VKPOS";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT KDKUNDNA,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,VKPART,"
                                                  + " VKPFARBE,VKPGROE,round(VKPMENGE1/100,3) AS QUANTITY,VKPMEEH,"
                                                  + " VKPKDBEZ,VKPKDARTNR,VKPKDFARBNR,VKPKDKOMM,VKPZUSATZ,KDNAME1"
                                                + " FROM VKPOS,KDSTAMM   WHERE VKPMANDANT = KDMANDANT "
                                                + " AND VKPKUNDNR = KDKUNDNR AND VKPMANDANT = '00' "
                                                + " AND TRIM(VKPSA)IS NULL AND VKPAUFART ='NA' "
                                                + " AND VKPAUFKRS = :VKPAUFKRS AND VKPAUFLFD = :VKPAUFLFD AND VKPAUFPOS = :VKPAUFPOS";

                orlReadCommand.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwWBBST["WBBAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwWBBST["WBBAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("VKPAUFPOS", OracleType.NVarChar).Value = dwWBBST["WBBAUFPOS"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
              
                byte[] b;
                string value;
               string ColumnName = "STATUS";
                foreach (DataRow dwTemp in setResult.Tables[TableName].Rows)
                {
                    ColumnName = "KDNAME1";
                    b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                    value = Encoding.GetEncoding("gb2312").GetString(b);
                    dwTemp[ColumnName] = value;
                }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet LoadVKPOS()
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT B.KDKUNDNA,A.VKPAUFKRS,A.VKPAUFLFD,A.VKPAUFPOS,A.VKPART,"
                                          + " A.VKPFARBE,A.VKPGROE,A.VKPMENGE1,A.VKPMEEH,A.VKPKDKOMM,A.VKPZUSATZ,"
                                          + " trim(A.VKPKDBEZ)||trim(A.VKPKDARTNR)||trim(VKPKDFARBNR) as STR"
                                          + " FROM INTEX.VKPOS A,INTEX.KDSTAMM B  WHERE A.VKPMANDANT = B.KDMANDANT "
                                          + " AND A.VKPKUNDNR = B.KDKUNDNR AND VKPMANDANT = '00' "
                                          + " AND VKPSA<>'AJ' AND VKPAUFART ='NA' "; 
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "VKPOS";
                    Queue Q100 = new Queue();
                    Q100.Enqueue("VKPMENGE1");
                    DivideBy100(setResult.Tables[0], Q100);

                    byte[] b;
                    string value;
                    string ColumnName = "KDKUNDNA";
                    foreach (DataRow dwTemp in setResult.Tables[0].Rows)
                    {

                        ColumnName = "KDKUNDNA";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        ColumnName = "STR"; 
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        ColumnName = "VKPFARBE"; 
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value; 
                    }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet BuildData(ref ArrayList listVV06, ref ArrayList ListColor, ref ArrayList listBatchNo,DataRow dwWBBST)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVVVKPOS(dwWBBST);
                setReturn.Merge(setResult);
                setResult = GetVVTLIEF(dwWBBST);
                setReturn.Merge(setResult); 
                setResult = GetWBBST(ref listVV06, ref ListColor, ref listBatchNo, dwWBBST);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}